package com.ligthsh.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * 一个操作数据库的帮助类
 * @author lightsh
 *
 */
public class MyDbHelper{
	
	private static final boolean DEBUG = true;
	private static final String TAG = "MyDbHelper";
	private static final int DATABASE_VERSION = 1;
	
	private static final String DATABASE_NAME = "my_db_name";
	public static final String TABLE_NAME = "my_table_name";
	
	// 数据表中的字段
	public static final String FIELD_ID = "_id";							//< 自增id值
	public static final String FIELD_NAME = "name";							//< 名称
	public static final String FIELD_TEXT = "text_value";					//< 内容
	public static final String FIELD_INT_VALUE = "int_value";				//< 一个整形值
	
	public static final String[] COLUMNS_ALL = {
		FIELD_ID,
		FIELD_NAME,
		FIELD_TEXT,
		FIELD_INT_VALUE
	};
	
	// 创建数据表db的sql语句(插入时id值传入null则自增)
	private static final String SQL_CREATE_TABLE = "create table " + TABLE_NAME + " ( " 
												+ FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
												+ FIELD_NAME + " VARCHAR(64),"
												+ FIELD_TEXT + " text,"
												+ FIELD_INT_VALUE + " INTEGER )";
	// 刷新数据表的sql语句
	private static final String SQL_UPGRADE_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
	
	
	private Context mContext = null;
	private MyDatabaseHelper mDatabaseHelper = null;
	
	private static void log(String msg){
		if(DEBUG && msg != null){
			Log.e(TAG, msg);
		}
	}
	
	private static class MyDatabaseHelper extends SQLiteOpenHelper{

		/**
		 * 如果传入的版本号跟旧的版本号不一致，则会执行{{@link #onUpgrade(SQLiteDatabase, int, int)}方法
		 * @param context
		 */
		public MyDatabaseHelper(Context context){
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			log("onCreate");
			db.execSQL(SQL_CREATE_TABLE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			log("onUpgrade");
			
			// 当数据库版本变更后，我们选择丢弃所有数据，并重新创建
			// FIXME 可以考虑根据版本号变更进行数据库修复操作
			db.execSQL(SQL_UPGRADE_TABLE);
			onCreate(db);			
		}
		
	}

	
	public MyDbHelper(Context context){
		mContext = context;
		
		// 创建该对象不会执行创建数据库的操作，不会耗费时间
		mDatabaseHelper = new MyDatabaseHelper(mContext);
	}
	
	private SQLiteDatabase getWritableDatabase(){
		return mDatabaseHelper.getWritableDatabase();
	}
	private SQLiteDatabase getReadableDatabase(){
		return mDatabaseHelper.getReadableDatabase();
	}
	
	/**
	 *  使用完，必须使用该方法关闭该数据库。多次调用该方法不会造成问题。</p>
	 *  一般的合理做法是: 始终在不使用该helper时调用该方法。
	 */
	public void close(){
		synchronized (this) {
			if(mDatabaseHelper != null){
				mDatabaseHelper.close();
				mDatabaseHelper = null;
			}
		}
	}
	
	/**
	 *  获取所有数据
	 */
	public Cursor fetchAllData(){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_NAME, COLUMNS_ALL, null, null, null, null, null);
	}
	
	/**
	 * 查询数据
	 * @param table The table name to compile the query against.
     * @param columns A list of which columns to return. Passing null will
     *            return all columns, which is discouraged to prevent reading
     *            data from storage that isn't going to be used.
     * @param selection A filter declaring which rows to return, formatted as an
     *            SQL WHERE clause (excluding the WHERE itself). Passing null
     *            will return all rows for the given table.
     * @param selectionArgs You may include ?s in selection, which will be
     *         replaced by the values from selectionArgs, in order that they
     *         appear in the selection. The values will be bound as Strings.
     * @param groupBy A filter declaring how to group rows, formatted as an SQL
     *            GROUP BY clause (excluding the GROUP BY itself). Passing null
     *            will cause the rows to not be grouped.
     * @param having A filter declare which row groups to include in the cursor,
     *            if row grouping is being used, formatted as an SQL HAVING
     *            clause (excluding the HAVING itself). Passing null will cause
     *            all row groups to be included, and is required when row
     *            grouping is not being used.
     * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
     *            (excluding the ORDER BY itself). Passing null will use the
     *            default sort order, which may be unordered.
     * @return A {@link Cursor} object, which is positioned before the first entry. Note that
     * {@link Cursor}s are not synchronized, see the documentation for more details.
	 */
	public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
	}
	
	/**
	 * 更新数据
	 * @param table the table to update in
     * @param values a map from column names to new column values. null is a
     *            valid value that will be translated to NULL.
     * @param whereClause the optional WHERE clause to apply when updating.
     *            Passing null will update all rows.
     * @return the number of rows affected
	 */
	public int update(String table, ContentValues values, String whereClause, String[] whereArgs){
		SQLiteDatabase db = getWritableDatabase();
		return db.update(table, values, whereClause, whereArgs);
	}
	
	/**
	 * 删除数据
	 * @param table the table to delete from
     * @param whereClause the optional WHERE clause to apply when deleting.
     *            Passing null will delete all rows.
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
	 */
	public int delete(String table, String whereClause, String[] whereArgs){
		SQLiteDatabase db = getWritableDatabase();
		return db.delete(table, whereClause, whereArgs);
	}
	
	/**
	 * 插入数据
	 * @param table the table to insert the row into
     * @param nullColumnHack SQL doesn't allow inserting a completely empty row,
     *            so if initialValues is empty this column will explicitly be
     *            assigned a NULL value
     * @param values this map contains the initial column values for the
     *            row. The keys should be the column names and the values the
     *            column values
     * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long insert(String table, String nullColumnHack, ContentValues values){
		SQLiteDatabase db = getWritableDatabase();
		return db.insert(table, nullColumnHack, values);
	}
	
	/**
	 * 插入数据
	 * 插入时id值传入null则自增
	 * @return 插入的row ID值, 如果出错返回-1
	 */
	public long insertData(String name, String text, int intVaule){
		SQLiteDatabase db = getWritableDatabase();
		ContentValues initValues = new ContentValues();
		initValues.put(FIELD_NAME, name);
		initValues.put(FIELD_TEXT, text);
		initValues.put(FIELD_INT_VALUE, intVaule);
		return db.insert(TABLE_NAME, null, initValues);
	}
	
	/**
	 *  删除指定的数据
	 * @param id	指定的id值
	 * @return		是否成功删除(如果没有指定数据，返回false)
	 */
	public boolean deleteData(int id){
		SQLiteDatabase db = getWritableDatabase();
		return db.delete(TABLE_NAME,
				FIELD_ID + "=" + id,
				null) > 0;
	}
	
	/**
	 * 获取指定id的数据
	 */
	public Cursor fetchData(int id){
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, 
				COLUMNS_ALL, 
				FIELD_ID + "=" + id , null, null, null, null);
		return cursor;
	}
	
	/**
	 * 查询指定id值是否存在
	 */
	public boolean checkIfExist(int id){
		Cursor cursor = fetchData(id);
		if(cursor != null ){
			if(cursor.getCount() > 0){
				cursor.close();
				return true;
			}else{
				cursor.close();
				return false;
			}
		}else{
			return false;
		}
	}	
	
	
	/**
	 *  执行一些sql语句(非查询的sql语句)
	 */
	public void execSql(String sql) throws SQLException{
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL(sql);
	}
	
	/**
	 *  执行自定义的查询语句
	 */
	public Cursor execSqlQuery(String sql){
		SQLiteDatabase db = getWritableDatabase();
		return db.rawQuery(sql, null);
	}
	
	/**
	 * 将结果打印
	 * @return
	 */
	public static final String toString(Cursor cursor){
		if(cursor != null && cursor.moveToFirst()){
			StringBuilder builder = new StringBuilder();
			
			int[] columnsIds = new int[COLUMNS_ALL.length];
			for(int i = 0; i < COLUMNS_ALL.length; ++i){
				columnsIds[i] = cursor.getColumnIndexOrThrow(COLUMNS_ALL[i]);
			}
			
			while(true){
				for(int i = 0; i < COLUMNS_ALL.length; ++i){
					builder.append(COLUMNS_ALL[i]).append(":").append(cursor.getString(columnsIds[i]));
					if(i != COLUMNS_ALL.length - 1){
						builder.append(",");
					}
				}
				if(!cursor.moveToNext()){
					break;
				}
				builder.append("\n");
			}
			return builder.toString();
		}
		return "";
	}
}

